﻿using System;
using System.Linq;
using System.Collections.Generic;
using System.Text;
using System.Data;
using Newtonsoft.Json;
using System.Data.SQLite;
using PDAMain.Entity;
using PDAMain.pdaWebReference;

namespace PDAMain.DAL.Dao
{
    class BaseDao
    {

        private string connectionString = "";
        public string ConnectionString
        {
            set { connectionString = value; }
        }

        /// <summary>
        /// 執行對數據庫操作的SQL
        /// </summary>
        /// <param name="strSql">SQL語句</param>
        /// <returns></returns>
        protected DataSet executeDataSet(string strSql) 
        {
            DataSet ds = new DataSet();
            SQLiteConnection conn = new SQLiteConnection();
            SQLiteCommand cmd = new SQLiteCommand();
            try
            {
                conn.ConnectionString = connectionString;

                if (conn.State.ToString() == "Closed")
                    conn.Open();

                cmd.CommandText = strSql;
                cmd.Connection = conn;

                SQLiteDataAdapter da = new SQLiteDataAdapter(cmd);
                da.Fill(ds);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
            return ds;
        }
        /// <summary>
        /// 執行對數據庫操作的SQL,
        /// 1、插入更新刪除數據
        /// 2、新建刪除數據表
        /// </summary>
        /// <param name="strSql">SQL語句</param>
        /// <returns></returns>
        protected object ExecuteScalar(string strSql)
        {
            int i = 0;
            object result = null;
            SQLiteConnection conn = new SQLiteConnection();
            System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand();
            try
            {
                conn.ConnectionString = connectionString;
                if (conn.State.ToString() == "Closed")
                    conn.Open();

                cmd.CommandText = strSql;
                cmd.Connection = conn;

                result = cmd.ExecuteScalar();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        /// <summary>
        /// 執行對數據庫操作的SQL,
        /// 1、插入更新刪除數據
        /// 2、新建刪除數據表
        /// </summary>
        /// <param name="strSql">SQL語句</param>
        /// <returns></returns>
        public bool executeNonQuery(string strSql)
        {
            int i = 0;
            bool Flag = false;
            SQLiteConnection conn = new SQLiteConnection();
            System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand();
            try
            {
                conn.ConnectionString = connectionString;
                if (conn.State.ToString() == "Closed")
                    conn.Open();

                cmd.CommandText = strSql;
                cmd.Connection = conn;

                i = cmd.ExecuteNonQuery();

                if (i > 0)
                    Flag = true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
            return Flag;
        }

        /// <summary>
        /// 待转换的Webservice数据
        /// </summary>
        /// <param name="strConvert">待转换的从webservice接收过来的字符串</param>
        /// <returns></returns>
        protected DataSet convertToDataSet(string strConvert)
        {
            DataSet returnDs = new DataSet();
            DataTable dt = new DataTable();
            if (strConvert == string.Empty)
                return null;
            try
            {
                List<Dictionary<string, string>> list = getDataList(strConvert);

                if (list == null)
                    return null;

                //表头
                foreach (Dictionary<string, string> dic in list)
                {
                    foreach (KeyValuePair<string, string> kvp in dic)
                    {
                        if (!dt.Columns.Contains(kvp.Key))
                            dt.Columns.Add(new DataColumn(kvp.Key));
                    }
                    break;
                }

                //数据

                foreach (Dictionary<string, string> dic in list)
                {
                    DataRow dr = dt.NewRow();
                    foreach (KeyValuePair<string, string> kvp in dic)
                    {
                        dr[kvp.Key] = kvp.Value;
                    }
                    dt.Rows.Add(dr);
                }
                returnDs.Tables.Add(dt);
                return returnDs;
            }
            catch
            {
                return null;
            }

        }

        protected List<Dictionary<string, string>> getDataList(string strlist)
        {
            List<Dictionary<string, string>> list = new List<Dictionary<string, string>>();
            JsonResult result = JsonConvert.DeserializeObject<JsonResult>(strlist);
            if (result.message == "")
            {
                list = result.data;
            }
            else
                list = null;
            return list;
        }


        protected PdaService getPdaWebService()
        {
            PdaService pda = new PdaService();
            string Webservice = ConfigHelper.GetConfigValue("Webservice");
            pda.Url = Webservice;
            return pda;
        }
    }
}
